<?php

/**
 * @author      Nguyen Duc Thuan <me@ndthuan.com>
 * @copyright   Copyright (c) 2009 Nguyen Duc Thuan <me@ndthuan.com>
 */
class Fete_Db
{
    protected $_linkId  = null;
    protected $_host    = null;
    protected $_user    = null;
    protected $_pass    = null;
    protected $_name    = null;
    protected $_insertId        = null;
    protected $_affectedRows    = null;
    protected $_autoReconnect = false;
    protected $_connectionEncoding = 'utf8';

    public function __construct($dbHost, $dbUser, $dbPass, $dbName = null)
    {
        $this->_host = $dbHost;
        $this->_user = $dbUser;
        $this->_pass = $dbPass;
        $this->_name = $dbName;
        
        $this->connect();
    }

    /**
     *
     * @param string $encoding
     * @return Fete_Db
     */
    public function &setConnectionEncoding($encoding)
    {
        $this->_connectionEncoding = $encoding;
        return $this;
    }

    /**
     *
     * @return Fete_Db
     */
    public function &enableAutoReconnection($enabled = true)
    {
        $this->_autoReconnect = $enabled;

        return $this;
    }

    public function &connect()
    {
        $this->_linkId = mysql_connect($this->_host, $this->_user, $this->_pass);

        if (!$this->_linkId) {
            throw new Fete_Db_Exception('Cannot connect to MySQL: '
                . mysql_error($this->_linkId));
        }

        if (!mysql_select_db($this->_name, $this->_linkId)) {
            throw new Fete_Db_Exception('Cannot use database ' . $this->_name
                . ': ' . mysql_error($this->_linkId));
        }

        return $this->execute("SET NAMES `$this->_connectionEncoding`");
    }

    /**
     *
     * @return Fete_Db
     */
    public function &disconnect()
    {
        mysql_close($this->_linkId);
        return $this;
    }

    public function query($sql)
    {        
        if ($this->_autoReconnect && !mysql_ping($this->_linkId)) {
            $this->disconnect()->connect();
        }

        $result = mysql_query($sql, $this->_linkId);
        if (!$result) {
            throw new Fete_Db_Exception(
                'Cannot query SQL statement. MySQL error: '
                . mysql_error($this->_linkId) . '. SQL: ' . $sql);
            return false;
        }
        return $result;
    }

    /**
     *
     * @param string $sql
     * @return Fete_Db
     */
    public function &execute($sql)
    {
        if ($this->_autoReconnect && !mysql_ping($this->_linkId)) {
            $this->disconnect()->connect();
        }

        if (!mysql_query($sql, $this->_linkId)) {
            throw new Fete_Db_Exception(
                'Cannot execute SQL statement. MySQL error: '
                . mysql_error($this->_linkId) . '. SQL: ' . $sql);
            return false;
        }
        switch (strtoupper(substr(trim($sql), 0, 7))) {
            case 'INSERT ':
                $this->_insertId = mysql_insert_id($this->_linkId);
                break;
            case 'UPDATE ':
            case 'DELETE ':
            default:
                $this->_affectedRows = mysql_affected_rows($this->_linkId);
                break;
        }
        return $this;
    }

    /**
     * @param $table
     * @param $data
     * @return Fete_Db
     */
    public function &insert($table, $data, $replace = false)
    {
        $fields = array();
        $values = array();

        foreach ($data as $field => $value)
        {
            $fields[] = '`' . $field . '`';
            $values[] = "'" . mysql_real_escape_string($value, $this->_linkId)
            . "'";
        }

        if (true === $replace) {
            $command = 'REPLACE';
        } else {
            $command = 'INSERT';
        }

        $this->execute("$command INTO $table (" . implode(', ', $fields)
            . ") VALUES (" . implode(', ', $values) . ")");
        

        return $this;
    }

    /**
     *
     * @return mixed
     */
    public function getInsertId()
    {
        return $this->_insertId;
    }

    /**
     * @param $table
     * @param $data
     * @param $conditions
     * @return Fete_Db
     */
    public function &update($table, $data, $conditions = null)
    {
        $updates = array();
        $conditions = $this->buildWhere($conditions);

        foreach ($data as $field => $value)
        {
            $updates[] = '`' . $field . '` = \''
            . mysql_real_escape_string($value, $this->_linkId) . '\'';
        }

        $this->execute("UPDATE $table SET " . implode(', ', $updates)
            . $conditions);

        return $this;
    }

    /**
     *
     * @return integer
     */
    public function getAffectedRows()
    {
        return $this->_affectedRows;
    }

    /**
     *
     * @param string $table
     * @param string|array $conditions
     * @return Fete_Db
     */
    public function &delete($table, $conditions = null)
    {
        $this->execute('DELETE FROM ' . $table . ' '
            . $this->buildWhere($conditions));
        $this->_affectedRows = mysql_affected_rows($this->_linkId);

        return $this;
    }

    public function getAll($sql, $fetchMode = MYSQL_ASSOC)
    {
        $rows = array();

        $result = $this->query($sql);

        while ($row = mysql_fetch_array($result, $fetchMode))
        {
            $rows[] = $row;
        }

        mysql_free_result($result);

        return $rows;
    }

    public function getFirst($sql, $fetchMode = MYSQL_ASSOC)
    {
        $result = $this->query($sql);
        $row = mysql_fetch_array($result, $fetchMode);

        mysql_free_result($result);

        return $row;
    }

    public function getValue($sql)
    {
        $row = $this->getFirst($sql, MYSQL_NUM);
        if ($row) {
            return $row[0];
        }
        return null;
    }

    public function getValues($sql)
    {
        $values = array();
        foreach ($this->getAll($sql, MYSQL_NUM) as $row)
        {
            $values[] = $row[0];
        }
        return $values;
    }

    public function fetchRow($result, $mode = MYSQL_ASSOC)
    {
        return mysql_fetch_array($result, $mode);
    }

    public function &free($result)
    {
        mysql_free_result($result);

        return $this;
    }

    public function buildWhere($conditions = null)
    {
        if (is_array($conditions)) {
            if (isset($conditions[0])) {
                return ' WHERE ' . implode(' AND ', $conditions);
            }
        } elseif (!empty($conditions) && is_string($conditions)) {
            return ' WHERE ' . $conditions;
        }

        return '';
    }

    /**
     * Builds ORDER BY clause
     *
     * @param array $sort
     * @return string
     */
    public function buildSort($sort)
    {
        if (is_array($sort) && count($sort) > 0) {
            $sortBy = array();
            foreach ($sort as $col => $ascending) {
                $sortBy[] = "$col $ascending";
            }
            return ' ORDER BY ' . implode(', ', $sortBy);
        } elseif (is_string($sort) && !empty($sort)) {
            return " ORDER BY $sort";
        } else {
            return '';
        }
    }
}